package com.example.demo.util;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel工具类
 * @author dyn
 * @version 2018/12/24
 *
 */
public class ExcelUtils {
	
	/**
	 * @title:	readExcel
	 * @author:	许子文
	 * @date:	2019年12月6日
	 * @description: 读取excel文件,并解析成集合
	 * @param excelPath excel文件的路径
	 * @param beanClass 解析成的目标对象
	 * @param headArgs 表格头字段,字段名称一定要与实体类中的一摸一样,顺序要和excel中的表头一样
	 */
	public static <T> List<T> readExcel(String excelPath,Class<T> beanClass,String[] headArgs) throws Exception{
		
		if(EmptyUtil.isEmpty(headArgs)) {
			throw new Exception("未传入表头结构");
		}
		
		InputStream input = new FileInputStream(excelPath);
		
		Workbook hssfWorkBook = null;
		
		if(excelPath.endsWith("xlsx")) {
			hssfWorkBook = new XSSFWorkbook(input);
		} else if (excelPath.endsWith("xls")) {
			hssfWorkBook = new XSSFWorkbook(input);
		}
		
		
		Map<String, String> rowData = null;
		
		Sheet sheet = hssfWorkBook.getSheetAt(0);
		
		if(null==sheet) {
			throw new Exception("未读取到工作簿");
		}
		
		List<T> list = new ArrayList<>();
		
		int excelRowNum = sheet.getLastRowNum();
		
		int headLen = headArgs.length;
		
		T data = null;
		
		for (int i = 1; i <= excelRowNum; i++) {
			Row row = sheet.getRow(i);
			
			if(null!=row) {
				rowData = new HashMap<>();
				
				data = beanClass.newInstance();
				
				for (int j = 0; j < headLen; j++) {
					String fieldName = headArgs[j];
					Cell cellValue = row.getCell(j);
					
					String fieldValue = getStringValueFromCell(cellValue);
					
					rowData.put(fieldName, fieldValue);
				}
				
				BeanUtils.populate(data, rowData);
				
				list.add(data);
			}
		}
		
		return list;
	}
	
	/**
	 * @title:	getStringValueFromCell
	 * @author:	许子文
	 * @date:	2019年12月9日
	 * @description: 转换excel中的类型成为java类型
	 */
	public static String getStringValueFromCell(Cell cell) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		DecimalFormat df = new DecimalFormat("#.#");
		
		String cellValue = "";
		
		if(null == cell) {
			return cellValue;
		}
		
		if(CellType.STRING == cell.getCellType()) {
			cellValue = cell.getStringCellValue();
			return cellValue;
		}
		
		if(CellType.NUMERIC == cell.getCellType()) {
			if(HSSFDateUtil.isCellDateFormatted(cell)) {
				double b = cell.getNumericCellValue();
				
				Date date = HSSFDateUtil.getJavaDate(b);
				
				cellValue = sdf.format(date);
			} else {
				cellValue = df.format(cell.getNumericCellValue());
			}
			
			return cellValue;
		}
		
		if(CellType.BLANK == cell.getCellType()) {
			return cellValue;
		}
		
		if(CellType.BOOLEAN == cell.getCellType()) {
			cellValue = String.valueOf(cell.getBooleanCellValue());
			return cellValue;
		}
		
		if(CellType.ERROR == cell.getCellType()) {
			return cellValue;
		}
		
		if(CellType.FORMULA == cell.getCellType()) {
			cellValue = cell.getCellFormula().toString();
			return cellValue;
		}
			
		return cellValue;
	}

}
